﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_SF_CE]
	-- Add the parameters for the stored procedure here
	(@CompanyId INT , 
	@CreateDate VARCHAR(10)
	)
AS
BEGIN
	
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @CreateDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @CreateDate + ' 23:59:59')
	
	--业绩变量
	DECLARE @IBC decimal(18,2),@WLSR decimal(18,2), @DRZJ  decimal(18,2), @ZRZJ decimal(18,2),@BH decimal(18,2),@IBC_Q decimal(18,2) ,@HK  decimal(18,2) ,@ZHIC decimal(18,2), @IBJ decimal(18,2) , @IAA decimal(18,2) ,@IAB decimal(18,2) ,@IAC decimal(18,2) ,@IAD decimal(18,2) ,@IAE decimal(18,2) ,@IAF decimal(18,2) ,@IAG decimal(18,2) ,@IBH decimal(18,2) ,@IBHA decimal(18,2) ,@IBHB decimal(18,2) ,@IBI decimal(18,2) ,@IBIA decimal(18,2) ,@IBIB decimal(18,2)
		
	Set @IBHA=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=1 and PaymentTypeId=1 and CurrentDate>=@sRq and CurrentDate<=@eRq),0)--当日现金收入平整
			
	Set @IBHB=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=2 and PaymentTypeId=1 and CurrentDate>=@sRq and CurrentDate<=@eRq),0)--当日现金支出平整
	
	Set @IBH=@IBHA-@IBHB--现金平整
	
	Set @IBIA=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=1 and PaymentTypeId<>1 and CurrentDate>=@sRq and CurrentDate<=@eRq),0)--当日银行收入平整
	
	Set @IBIB=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=2 and PaymentTypeId<>1 and CurrentDate>=@sRq and CurrentDate<=@eRq),0)--当日银行支出平整
	
	Set @IBI=@IBIA-@IBIB--银行平整

	SET @IBC=( ISNULL((SELECT IBC FROM Statistics_Financial WHERE CompanyId=@companyId and CreateDate>=@sRq and CreateDate<=@eRq ),0))
	SET @IBC_Q=( ISNULL((SELECT IBC FROM Statistics_Financial WHERE CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=Convert(nchar(10),dateadd(d,-1,CONVERT(DATETIME,@CreateDate)),120)),0))
	SET @ZHIC=ISNULL((SELECT sum(fo.OutlayPrice) FROM Finance_Outlay fo WHERE CompanyIdcur=@companyId and convert(varchar(10),fo.CreateDate,120)=@CreateDate AND CateOneId!=19),0)
	SET @HK=ISNULL((SELECT sum(l.[Money]) FROM Loan l WHERE l.flag=1 and  Convert(nchar(10),l.HKDate,120)=@CreateDate AND l.CompanyId=@CompanyId),0)
	SET @BH=ISNULL((Select Sum(CostPrice) From Project Where CompanyId =@CompanyId and Convert(nchar(10),FinanceAuditDate,120)=@CreateDate AND Convert(nchar(10),CreateDate,120)<>@CreateDate and FinanceAudit=2    ),0) 
	SET @DRZJ=isnull((SELECT IBA+ibb FROM Statistics_Financial sf WHERE CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate),0)
	SET @ZRZJ=isnull((SELECT IBA+ibb FROM Statistics_Financial sf WHERE CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=Convert(nchar(10),dateadd(d,-1,CONVERT(DATETIME,@CreateDate)),120)),0)
	SET @WLSR=	ISNULL((SELECT SUM(ld.je)  FROM L_Dealings ld WHERE ld.I_CompanyId=@CompanyId AND CONVERT(VARCHAR(10),ld.CreateDate,120)=@CreateDate),0)
	
	--Set @IAA=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate and TypeId=1 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag<>2 ),0)+
	--		ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge WHERE CompanyId=@companyId and Convert(nchar(10),UpTime,120)=@CreateDate  and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )),0)--新增毛利AND Project.NOE_Flag<>2|and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
			
	--		Set @IAB=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate and TypeId=2 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag<>2 ),0)+
	--				ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge WHERE CompanyId=@companyId and Convert(nchar(10),UpTime,120)=@CreateDate  and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )),0)--续费毛利
		
	--		Set @IAC=ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From [Order] WHERE CompanyId=@CompanyId and Convert(nchar(10),AuditDate,120)=@CreateDate AND StateId>=1  AND NOE_Flag<>2 ),0)+
	--		ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge WHERE CompanyId=@companyId and Convert(nchar(10),UpTime,120)=@CreateDate  and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )),0)--新增应收

	--		Set @IAD=ISNULL((Select Sum(CostPrice) From Project Where CompanyId =@CompanyId and Convert(nchar(10),CreateDate,120)=@CreateDate and FinanceAudit=1   ),0)+
	--		ISNULL((SELECT sum(dch_yf) FROM Order_Change_Hedge WHERE CompanyId=@companyId and Convert(nchar(10),UpTime,120)=@CreateDate  ),0)   --新增应付

	--	    --Set @IAE=ISNULL((Select Sum(YingShouPrice) From [Order] Where CompanyId=@CompanyId and Convert(nchar(10),FinanceRenlingDate,120)=@CreateDate),0)--成单金额
	--	    Set @IAE=ISNULL((Select Sum(F_YingShou) From [Order] Where CompanyId=@CompanyId AND StateId>=1 and Convert(nchar(10),AuditDate,120)=@CreateDate  AND NOE_Flag<>2 ),0)+
	--	    ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge WHERE CompanyId=@companyId and Convert(nchar(10),UpTime,120)=@CreateDate  and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )),0)--成单金额

			
	--		Set @IAF=ISNULL((Select Sum(Prices) From Order_PaymentRecord Where CompanyId=@companyId and Convert(nchar(10),FinanceDate,120)=@CreateDate and SWId=2   and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2  )),0)+
	--		ISNULL((SELECT sum(dch_wk) FROM Order_Change_Hedge WHERE CompanyId=@companyId and Convert(nchar(10),UpTime,120)=@CreateDate  and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )),0)--尾款收入
			
	--		DECLARE @iaf_a DECIMAL(18,2)
	--		set @iaf_a=ISNULL((SELECT SUM(prices) FROM Order_PaymentRecord WHERE CompanyId=@CompanyId AND Convert(nchar(10),FinanceDate,120)=@CreateDate and SWId=2 AND oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 AND CONVERT(VARCHAR(10),o.AuditDate,120)=@CreateDate AND o.StateId>=1 )),0)
	--		SET @IAF=@IAF-@iaf_a	--当日成单当日收回来的尾款，不参与计算	，算在当日的实收里	
			
	--		Set @IAG=ISNULL((SELECT SUM(p.SalePrice-p.CostPrice) FROM Project p WHERE p.ProductId IN (22,29,33) AND p.CompanyId=@CompanyId AND CONVERT(VARCHAR(10),p.CreateDate,120)=@CreateDate    ),0)--非营业性收入
Set @IAA=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=1 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag<>2 ),0)+
			ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)--新增毛利AND Project.NOE_Flag<>2|and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
			
			Set @IAB=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=2 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag<>2 ),0)+
					ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2),0)--续费毛利
			
			Set @IAC=ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From [Order] WHERE CompanyId=@CompanyId and AuditDate>=@sRq and AuditDate<=@eRq AND StateId>=1  AND NOE_Flag<>2 ),0)+
			ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid  and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)--新增应收

			Set @IAD=ISNULL((Select Sum(CostPrice) From Project Where CompanyId =@CompanyId and FinanceAudit=1 and  CreateDate>=@sRq and CreateDate<=@eRq ),0)+
			ISNULL((SELECT sum(dch_yf) FROM Order_Change_Hedge WHERE CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  ),0)   --新增应付


		    Set @IAE=ISNULL((Select Sum(F_YingShou) From [Order] Where CompanyId=@CompanyId AND StateId>=1 and AuditDate>=@sRq and AuditDate<=@eRq  AND NOE_Flag<>2 ),0)+
		    ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and Order_Change_Hedge.UpTime>=@sRq and Order_Change_Hedge.UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)--成单金额

			
			Set @IAF=ISNULL((Select Sum(Prices) From Order_PaymentRecord inner JOIN [Order] o on  Order_PaymentRecord.oid = o.oid and Order_PaymentRecord.CompanyId=@companyId and FinanceDate>=@sRq and FinanceDate<=@eRq and SWId=2 and  o.companyId=@companyId AND o.NOE_Flag<>2),0)+
			ISNULL((SELECT sum(dch_wk) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)--尾款收入
			
			DECLARE @iaf_a DECIMAL(18,2)
			
			set @iaf_a=ISNULL((SELECT SUM(Order_PaymentRecord.prices) FROM Order_PaymentRecord inner JOIN [Order] o on Order_PaymentRecord.oid =o. oid AND  Order_PaymentRecord.CompanyId=@CompanyId 
					  AND o.StateId>=1 and o.CompanyId=@CompanyId AND FinanceDate>=@sRq and FinanceDate<=@eRq  and SWId=2   AND o.NOE_Flag<>2 AND o.AuditDate>=@sRq and o.AuditDate<=@eRq ),0)

			SET @IAF=@IAF-@iaf_a	--当日成单当日收回来的尾款，不参与计算	，算在当日的实收里	
			
			Set @IAG=ISNULL((SELECT SUM(p.SalePrice-p.CostPrice) FROM Project p WHERE p.ProductId IN (22,29,33) AND p.CompanyId=@CompanyId AND CreateDate>=@sRq and CreateDate<=@eRq   ),0)--非营业性收入

	
	Set @IBJ=(@DRZJ
	           -@ZRZJ)-
			((@IAA+      @IAB+      @IAF-     @IAC+      @IAD+      @IBH+      @IBI
			
			-@IBC
			+@IBC_Q
			-@ZHIC
			-@BH
			+@HK
		    +@WLSR
			+@IAG
			))
	
	--SELECT @DRZJ 当日资金,@ZRZJ 昨日资金 ,@IAA 新增毛利 ,@IAB 续费毛利,@IAC 新增应收 ,@IAD 新增应付 
	--,@IAE 成单金额 ,@IAF 尾款收入 
	--,@IAG 非营业性收入 ,@IBC 当日账户余额,@IBC_Q 昨天账户余额 ,@ZHIC 支出,@HK 还款,@BH 补划	
	--,@IBJ 差额
	--
	SELECT @DRZJ  AS DRZJ,@ZRZJ  AS ZRZJ,@IAA  AS IAA,@IAB AS IAB ,@IAC AS IAC ,@IAD AS IAD  
	,@IAE AS IAE ,@IAF  AS IAF,@WLSR wlsr,@IBH as IBH,@IBI AS IBI
	,@IAG AS IAG ,@IBC AS IBC,@IBC_Q AS IBC_Q ,@ZHIC AS ZHIC,@HK AS HK,@BH 	AS BH
	,@IBJ AS IBJ,(SELECT top 1 CompanyName FROM company WHERE id=@CompanyId ) AS CompanyName
	
END
